﻿if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_mystery_visitor_grade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_mystery_visitor_grade]
GO

CREATE TABLE [dbo].[hrm_mystery_visitor_grade] (
	[visitor_area] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_oversee] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_province] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_city] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_city_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_dealer_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_address] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_position_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_acershop_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_emergency] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_sum_grade] [float] NULL ,
	[v_oversee_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_record_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_sort] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[vgid] [bigint] IDENTITY (1, 1) NOT NULL ,
	[createtime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrm_mystery_visitor_tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrm_mystery_visitor_tmp]
GO

CREATE TABLE [dbo].[hrm_mystery_visitor_tmp] (
	[visitor_area] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[shop_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_oversee] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_province] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_city] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_city_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_dealer_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_address] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_position_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_acershop_level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_emergency] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_sum_grade] [float] NULL ,
	[v_oversee_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_record_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_shop_sort] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[v_sessionid] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[vgid] [bigint] IDENTITY (1, 1) NOT NULL ,
	[createtime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

----------------------------------------------------------------------------------------------------



insert into hrm_mystery_visitor_grade values
(1, '华东', '5','2011-03','李勇','北京','北京','1','哈尔滨宏联基业科技开发有限公司',
'黑龙江宏联-哈尔滨-百脑汇-门店1-1B09','哈尔滨-百脑汇','Heilongjiang','ACER','装修',89,'0','小张');

select visitor_area from hrm_mystery_visitor_tmp 
	where visitor_area not in (select distinct shop_area_value from hrm_shop_info)

select shop_id from hrm_mystery_visitor_tmp 
	where shop_id not in (select distinct shop_id from hrm_shop_info)

select v_oversee from hrm_mystery_visitor_tmp 
	where v_oversee not in (select distinct shop_ctm_name from hrm_shop_info)

	
	
select v_oversee from hrm_mystery_visitor_tmp
	 where v_sessionid='0350864E3A34490DA491EE63D8A3AF371300609257687'
		 and v_oversee not in (select distinct shop_ctm_name from hrm_shop_info)


insert into hrm_mystery_visitor_grade(visitor_area,shop_id,v_date,v_oversee,v_province,v_city,v_city_level,
	v_dealer_name,v_shop_name,v_shop_address,v_position_code,v_acershop_level,v_emergency,
	v_sum_grade,v_oversee_id,v_record_name,v_shop_sort) 
		select visitor_area,shop_id,v_date,v_oversee,v_province,v_city,v_city_level,
			v_dealer_name,v_shop_name,v_shop_address,v_position_code,v_acershop_level,v_emergency,
			v_sum_grade,v_oversee_id,v_record_name,v_shop_sort from hrm_mystery_visitor_tmp 
				where v_sessionid='6F483EC8E46EE9889372AA4DC7127E2F1300610350031'


alter table hrm_mystery_visitor_tmp drop column vgid
alter table hrm_mystery_visitor_tmp
 add vgid  bigint identity(1,1) primary key not null

alter table hrm_mystery_visitor_grade drop columns vgid  
alter table hrm_mystery_visitor_grade add vgid  bigint identity(1,1) primary key not null








